package com.hnjowe.webdb;

import java.sql.Timestamp;
import java.sql.Date;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.CallableStatement;
import java.sql.SQLException;

/**
 * 数据库操作封装类：CallableStament的封装
 * 用于操作存储过程
 * @author 王秀武
 * @version 1.0
 */
public class DBOperation_call {
    private Connection conn;
    private CallableStatement call;

    public DBOperation_call() {
        DBConnection dbConnection = new DBConnection();
        try {
            conn = dbConnection.getConnection();
        }
        catch (Exception ex) {
            //Debug.println("cann't get connection from database server while connetion to database"+ex);
        }
    }

    /**
     * 连接事物开始
     * @return result
     */
    public boolean transcationBegin() {
        boolean result =false;
        try {
            if (conn != null) {
                conn.setAutoCommit(false);
                result = true;
            } else {
                //Debug.println("cann't get connection from database server");
            }
        } catch(SQLException ex) {
            result =false;
            //Debug.println("transcation set auto commit true error:"+ex);
        }
        return  result;
    }

    /**
     * 事物结束
     * @return result
     */
    public boolean transcationEnd(){
        boolean result =false;
        try {
            if (conn != null) {
                conn.setAutoCommit(true);
                result = true;
            } else {
                //Debug.println("cann't get connection from database server");
            }
        } catch(SQLException ex) {
            result =false;
            //Debug.println("transcation set auto commit false error:"+ex);
        }
        return result;
    }

    /**
     * 事物提交
     * @return result
     */
    public boolean transcationCommit(){
        boolean result =false;
        try {
            if (conn != null) {
                conn.commit();
                result = true;
            } else {
                // Debug.println("cann't get connection from database server");
            }
        } catch(SQLException ex) {
            result =false;
            //Debug.println("transcation commit error:"+ex);
        }
        return result;
    }

    /**
     * 事物回退
     * @return result
     */
    public boolean transcationRollback(){
        boolean result =false;
        try {
            if (conn != null) {
                conn.rollback();
                result = true;
            } else {
                // Debug.println("cann't get connection from database server");
            }
        } catch(SQLException ex) {
            result =false;
            // Debug.println("transcation rollback error:"+ex);
        }
        return result;
    }

    /**
     * 设定参数
     * @param index int
     * @param value long
     */
    public void setLong(int index,long value) {
        try {
            if (call != null){
                call.setLong(index,value);
            }else {
                //Debug.println("cann't get preparestatment from connection");
            }
        } catch(SQLException ex) {
            // Debug.println("cann't get pres from connection"+ex);
        }
    }

    /**
     * 设定参数
     * @param index int
     * @param value int
     */
    public void setInt(int index,int value) {
        try {
            if (call != null){
                call.setInt(index,value);
            }else {
                //Debug.println("cann't get preparestatment from connection");
            }
        } catch(SQLException ex) {
            // Debug.println("cann't get pres from connection"+ex);
        }
    }

    /**
     * 设定参数
     * @param index int
     * @param value float
     */
    public void setFloat(int index,float value) {
        try {
            if (call != null){
                call.setFloat(index,value);
            }else {
                // Debug.println("cann't get preparestatment from connection");
            }
        } catch(SQLException ex) {
            //Debug.println("cann't get pres from connection"+ex);
        }
    }

    /**
     * 设定参数
     * @param index int
     * @param value double
     */
    public void setDouble(int index,double value) {
        try {
            if (call != null){
                call.setDouble(index,value);
            }else {
                //  Debug.println("cann't get preparestatment from connection");
            }
        } catch(SQLException ex) {
            // Debug.println("cann't get pres from connection"+ex);
        }
    }

    /**
     * 设定参数
     * @param index int
     * @param value String
     */
    public void setString(int index,String value) {
        try {
            if (call != null){
                call.setString(index,value);
            }else {
                //  Debug.println("cann't get preparestatment from connection");
            }
        } catch(SQLException ex) {
            // Debug.println("cann't get pres from connection"+ex);
        }
    }

    /**
     * 设定参数
     * @param index int
     * @param value byte[]
     */
    public void setBytes(int index, byte[] value) {
        try {
            if (call != null) {
                call.setBytes(index, value);
            }
            else {
                // Debug.println("can't get preparestatement from connection");
            }
        }
        catch (SQLException ex) {
            // Debug.println("can't get pres from connection"+ex);
        }
    }

    /**
     * 设定参数
     * @param index int
     * @param value int
     */
    public void setNull(int index,int value) {
        try {
            if (call != null){
                call.setNull(index,value);
            }else {
                //  Debug.println("cann't get preparestatment from connection");
            }
        } catch(SQLException ex) {
            // Debug.println("cann't get pres from connection"+ex);
        }
    }

    /**
     * 设定参数
     * @param index int
     * @param value Date
     */
    public void setDate(int index, Date value) {
        try {
            if (call != null) {
                call.setDate(index, value);
            }
            else {
                // Debug.println("cann't get preparestatment from connection");
            }
        }
        catch (SQLException ex) {
            //Debug.println("cann't get pres from connection"+ex);
        }
    }


    /**
     * 设定参数
     * @param index int
     * @param value Timestamp
     */
    public void setTimestamp(int index,Timestamp value) {
        try {
            if (call != null){
                call.setTimestamp(index,value);
            }else {
                // Debug.println("cann't get preparestatment from connection");
            }
        } catch(SQLException ex) {
            //Debug.println("cann't get pres from connection"+ex);
        }
    }

    /**
     * 获取值
     * @param index int
     * @return val
     */
    public int getInt(int index){
        int val = 0;
        try{
            if (call !=null){
                val = call.getInt(index);
            }else{
                //System.out.println("can't get prepareCall from connection");
            }
        }catch(SQLException ex){
            System.out.println("err "+ex);
        }
        return val;
    }

    /**
     * 获取值
     * @param index int
     * @return val
     */
    public Date getDate(int index){
        Date val = null;
        try{
            if (call !=null){
                val = call.getDate(index);
            }else{
                //System.out.println("can't get prepareCall from connection");
            }
        }catch(SQLException ex){
            System.out.println("err "+ex);
        }
        return val;
    }

    /**
     * 获取值
     * @param index int
     * @return val
     */
    public double getDouble(int index){
        double val = 0.0;
        try{
            if (call !=null){
                val = call.getDouble(index);
            }else{
                //System.out.println("can't get prepareCall from connection");
            }
        }catch(SQLException ex){
            System.out.println("err "+ex);
        }
        return val;
    }

    /**
     * 获取值
     * @param index int
     * @return val
     */
    public float getFloat(int index){
        float val = 0;
        try{
            if (call !=null){
                val = call.getFloat(index);
            }else{
                //System.out.println("can't get prepareCall from connection");
            }
        }catch(SQLException ex){
            System.out.println("err "+ex);
        }
        return val;
    }

    /**
     * 获取值
     * @param index int
     * @return val
     */
    public long getLong(int index){
        long val = 0;
        try{
            if (call !=null){
                val = call.getLong(index);
            }else{
                //System.out.println("can't get prepareCall from connection");
            }
        }catch(SQLException ex){
            System.out.println("err "+ex);
        }
        return val;
    }

    /**
     * 获取值
     * @param index int
     * @return val
     */
    public byte	getByte(int index){
        byte val = 0;
        try{
            if (call !=null){
                val = call.getByte(index);
            }else{
                //System.out.println("can't get prepareCall from connection");
            }
        }catch(SQLException ex){
            System.out.println("err "+ex);
        }
        return val;
    }

    /**
     * 获取值
     * @param index int
     * @return val
     */
    public String getString(int index){
        String val = null;
        try{
            if (call !=null){
                val = call.getString(index);
            }else{
                //System.out.println("can't get prepareCall from connection");
            }
        }catch(SQLException ex){
            System.out.println("err "+ex);
        }
        return val;
    }

    /**
     * 获取值
     * @param index int
     * @return val
     */
    public Timestamp getTimestamp(int index){
        Timestamp val = null;
        try{
            if (call != null){
                val = call.getTimestamp(index);
            }else{
                //System.out.println("can't get prepareCall from connection");
            }
        }catch(SQLException ex){
            System.out.println("err "+ex);
        }
        return val;
    }

    /**
     * OUT
     * @param index int
     * @param sqlType int
     */
    public void registerOutParameter(int index, int sqlType){
        try{
            if(call != null){
                call.registerOutParameter(index,sqlType);
            }else{
                System.out.println("can't registerOut Parameter!");
            }
        }catch(SQLException ex){
            System.out.println(ex);
        }
    }

    /**
     * OUT
     * @param index int
     * @param sqlType int
     * @param scale int
     */
    public void registerOutParameter(int index, int sqlType, int scale){
        try{
            if(call != null){
                call.registerOutParameter(index,sqlType,scale);
            }else{
                System.out.println("can't registerOut Parameter!");
            }
        }catch(SQLException ex){
            System.out.println(ex);
        }
    }

    /**
     * OUT
     * @param index int
     * @param sqlType int
     * @param typeName String
     */
    public void registerOutParameter(int index, int sqlType, String typeName){
        try{
            if(call != null){
                call.registerOutParameter(index,sqlType,typeName);
            }else{
                System.out.println("can't registerOut Parameter!");
            }
        }catch(SQLException ex){
            System.out.println(ex);
        }
    }

    /**
     * 获得一个callableStatement对象
     * @param callSql String
     */
    public void callableStatement(String callSql) {
        try {
            if (conn != null){
                call = conn.prepareCall(callSql);
            }else {
                // Debug.println("cann't get connection from database server");
            }
        } catch(SQLException ex) {
            // Debug.println("execute update string error:"+ex);
        }
    }

    /**
     * 提交查询
     * @return res
     */
    public ResultSet executeQuery() {
        ResultSet res = null;
        try {
            if (call != null) {
                //pres = conn.prepareStatement(querySql);
                res  = call.executeQuery();
            }else {
                // Debug.println("cann't get connection from database server");
            }
        } catch (SQLException ex) {
            //Debug.println("execute query string error:"+ex);
        }
        return res;
    }

    /**
     * 提交修改
     * @return result
     */
    public int executeUpdate(){
        int result = -1;
        try {
            if (call != null){
                //System.out.println("Insert seccess!");//test case!
                result = call.executeUpdate();
                if(result==-1)
                    System.out.println("Update fail!");//test case!
            }else {
                // Debug.println("cann't get prepareStament from database server");
            }
            //closeConn();
        } catch(SQLException ex) {
            result = -1;
            // Debug.println("execute update string error:"+ex);
        }
        System.out.println(result);
        return result;
    }
    /**
     * 提交
     * @return result
     */
    public boolean execute(){
        boolean result = false;
        try {
            if (call != null){
                //System.out.println("Insert seccess!");//test case!
                result = call.execute();
                //if(result==false)
                //System.out.println(result +"execute fail!");//test case!
            }else {
                // Debug.println("cann't get prepareStament from database server");
            }
            //closeConn();
        } catch(SQLException ex) {
            result = false;
            // Debug.println("execute update string error:"+ex);
        }
        //System.out.println(result);
        return result;
    }

    /**
     * 关闭连接
     */
    public void closeConn() {
        try {
            if ( conn != null && conn.isClosed()==false)
                conn.close();
        } catch(SQLException ex) {
            //Debug.println("cann't close connection error:"+ex);
        }
    }

    /**
     * 关闭callableStatement，在关闭连接之前使用
     */
    public void closeCall() {
        try {
            if ( call != null)
                call.close();
        } catch(SQLException ex) {
            //Debug.println("cann't close connection error:"+ex);
        }
    }
}